﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace BussinessLayer
{
    public class tblHocsinh_giaovien
    {

        DataLayer.Data odata = new DataLayer.Data();
        public DataTable Selecthocsinh_giaovien()
        {
            return odata.view_text("SELECT * FROM hocsinh_giaovien WHERE not exists (select * from nguoidung Where hocsinh_giaovien.id = nguoidung.id)");
            //return odata.view_text("SELECT *FROM hocsinh_giaovien");
        }
        public DataTable SelectHocsinhChidoan(string _namhoc)
        {
            return odata.view_text("SELECT * FROM hocsinh_giaovien AS hsgv LEFT JOIN (SELECT hscd.id, hscd.chidoan_id, cd.khoi, cd.lop FROM hocsinh_chidoan AS hscd LEFT JOIN chidoan AS cd ON hscd.chidoan_id=cd.chidoan_id WHERE cd.namhoc='" + _namhoc + "' ) AS hs ON hsgv.id=hs.id LEFT JOIN doanvien AS dv ON hsgv.id=dv.id WHERE hsgv.trangthai='True'");
        }
        public DataTable selectAlldoanvien()
        {
            return odata.view_text("SELECT *FROM hocsinh_giaovien,doanvien,doanvien_chidoan,chidoan where hocsinh_giaovien.id=doanvien.id and doanvien.id=doanvien_chidoan.id and doanvien_chidoan.chidoan_id=chidoan.chidoan_id");
        }


        public DataTable SelectHocSinh()
        {
            return odata.view_text("SELECT * FROM hocsinh_giaovien AS hsgv LEFT JOIN hocsinh_chidoan AS hscd ON hsgv.id=hscd.id LEFT JOIN chidoan AS cd ON hscd.chidoan_id=cd.chidoan_id WHERE hsgv.lahocsinh='True' AND hsgv.id NOT IN (SELECT id FROM doanvien)");
        }

        public void hocsinh_giaovien(int _id, string _hoten, DateTime _ngaysinh, bool _gioitinh, string _noisinh, string _dantoc, string _tongiao, bool _lahocsinh, bool _trangthai)
        {
            SqlParameter[] sp = new SqlParameter[9];
            sp[0] = new SqlParameter("@id", _id);
            sp[1] = new SqlParameter("@hoten", _hoten);
            sp[2] = new SqlParameter("@ngaysinh", _ngaysinh);
            sp[3] = new SqlParameter("@gioitinh", _gioitinh);
            sp[4] = new SqlParameter("@noisinh", _noisinh);
            sp[5] = new SqlParameter("@dantoc", _dantoc);
            sp[6] = new SqlParameter("@tongiao", _tongiao);
            sp[7] = new SqlParameter("@lahocsinh", _lahocsinh);
            sp[8] = new SqlParameter("@trangthai", _trangthai);
            odata.proc_TS("tblhocsinh_giaovien", sp);
        }
    }
}
